package zozopo.attendance.operations;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.operations.comm.OperatingService;

public class LeaveOS extends OperatingService{
	
	public LeaveOS(Connection conn){
		super(conn);
	}
	
	public LeaveOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<LeaveTB> selectListBySql (String sql){
		List<LeaveTB> list = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		//Map flowMap = flow.getFlowStateMapByKind(FlowCode.HOUSEWARE_RECEIPTLIST);
		if(resultSet != null){
				try {
					list = new ArrayList<LeaveTB>();
					while(resultSet.next()){
						LeaveTB obj = new LeaveTB();
							
						
						obj.setId_leave(resultSet.getInt(LeaveTB.ID_LEAVE));
						
						obj.setOperatorid_leave(resultSet.getInt(LeaveTB.OPERATORID_LEAVE));
						obj.setName_leave(resultSet.getString(LeaveTB.NAME_LEAVE));
						obj.setFirstname_leave(resultSet.getString(LeaveTB.FIRSTNAME_LEAVE));
						obj.setLastname_leave(resultSet.getString(LeaveTB.LASTNAME_LEAVE));
						
						
						obj.setAppoperatorid_leave(resultSet.getInt(LeaveTB.APPOPERATORID_LEAVE));
						obj.setAppname_leave(resultSet.getString(LeaveTB.APPNAME_LEAVE));
						obj.setAppfirstname_leave(resultSet.getString(LeaveTB.APPFIRSTNAME_LEAVE));
						obj.setLastname_leave(resultSet.getString(LeaveTB.APPLASTNAME_LEAVE));
						obj.setAppdate_leave(resultSet.getString(LeaveTB.APPDATE_LEAVE));
					
						
						obj.setIsapp_leave(resultSet.getBoolean(LeaveTB.ISAPP_LEAVE));
						obj.setIscancel_leave(resultSet.getBoolean(LeaveTB.ISCANCEL_LEAVE));
						
						obj.setCreatedate_leave(resultSet.getString(LeaveTB.CREATEDATE_LEAVE));
						
						obj.setDate_leave(resultSet.getString(LeaveTB.DATE_LEAVE));
						obj.setBegin_leave(resultSet.getString(LeaveTB.BEGIN_LEAVE));
						obj.setEnd_leave(resultSet.getString(LeaveTB.END_LEAVE));
						
						obj.setReason_leave(resultSet.getString(LeaveTB.REASON_LEAVE));
						
						obj.setDepartmentid_leave(resultSet.getInt(LeaveTB.DEPARTMENTID_LEAVE));
						obj.setDepartmentlevel_leave(resultSet.getInt(LeaveTB.DEPARTMENTLEVEL_LEAVE));
						obj.setDepartmentname_leave(resultSet.getString(LeaveTB.DEPARTMENTNANME_LEAVE));
						

						list.add(obj);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return list;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<LeaveTB> selectAll(){
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME;
		List<LeaveTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	
	
	
	public LeaveTB get(int id){
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME + " where " + LeaveTB.ID_LEAVE + "=" + id;
		LeaveTB obj = new LeaveTB();
		List<LeaveTB> list = selectListBySql(sql);
		obj = list.get(0); 
		return obj;
	}
	
	public LeaveTB getInfoByOperatorid(int operatorid){
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME + " where " + LeaveTB.OPERATORID_LEAVE + "=" + operatorid;
		LeaveTB obj = new LeaveTB();
		List<LeaveTB> list = selectListBySql(sql);
		obj = list.get(0); 
		return obj;
	}
	
	/**
	 * @version 1.0
	 * @see Gee
	 * */
	public boolean isExistDate(String date,int operatorid){
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME + " where " + LeaveTB.OPERATORID_LEAVE + "=" + operatorid
		+ " AND " + LeaveTB.DATE_LEAVE + "='" +date+"'";;
		List<LeaveTB> list = selectListBySql(sql);
		if(list.size()>=1){
			return true;
		}else{
			return false;
		}
	}
	
	
	

	
	public List<LeaveTB> selectAll(int beginPageNum,int pageSize){
		
		List<LeaveTB> list = null;
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME  
		+ " ORDER BY " + LeaveTB.ID_LEAVE + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + LeaveTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	public List<LeaveTB> selectByOperatorid(int operatorid,int beginPageNum,int pageSize){
		
		List<LeaveTB> list = null;
		String sql = "SELECT * FROM " + LeaveTB.TABLE_NAME  
		+ " where " + LeaveTB.OPERATORID_LEAVE +"="+operatorid
		+ " ORDER BY " + LeaveTB.ID_LEAVE + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumByOperatorid(int operatorid){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + LeaveTB.TABLE_NAME 
		+ " where " + LeaveTB.OPERATORID_LEAVE +"="+operatorid;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}

	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<LeaveTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+LeaveTB.TABLE_NAME+" WHERE  "
		
		+ LeaveTB.NAME_LEAVE+" LIKE '%"+searchStr+"%'"
		+" OR "+LeaveTB.FIRSTNAME_LEAVE+" LIKE '%"+searchStr+"%'"
		+" OR "+LeaveTB.LASTNAME_LEAVE+" LIKE '%"+searchStr+"%'"
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + LeaveTB.TABLE_NAME +" WHERE  "

		+ LeaveTB.NAME_LEAVE+" LIKE '%"+searchStr+"%'"
		+" OR "+LeaveTB.FIRSTNAME_LEAVE+" LIKE '%"+searchStr+"%'"
		+" OR "+LeaveTB.LASTNAME_LEAVE+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean apply(LeaveTB leave){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();
		
		
		insertMap.put(LeaveTB.DEPARTMENTID_LEAVE, leave.getDepartmentid_leave());
		insertMap.put(LeaveTB.DEPARTMENTLEVEL_LEAVE, leave.getDepartmentlevel_leave());
		insertMap.put(LeaveTB.DEPARTMENTNANME_LEAVE, leave.getDepartmentname_leave());
		
		insertMap.put(LeaveTB.OPERATORID_LEAVE, leave.getOperatorid_leave());
		insertMap.put(LeaveTB.NAME_LEAVE, leave.getName_leave());
		insertMap.put(LeaveTB.FIRSTNAME_LEAVE, leave.getFirstname_leave());
		insertMap.put(LeaveTB.LASTNAME_LEAVE, leave.getLastname_leave());
		
		insertMap.put(LeaveTB.DATE_LEAVE, leave.getDate_leave());
		insertMap.put(LeaveTB.BEGIN_LEAVE, leave.getBegin_leave());
		insertMap.put(LeaveTB.END_LEAVE, leave.getEnd_leave());
		
		insertMap.put(LeaveTB.REASON_LEAVE, leave.getReason_leave());
		
		return sqlMachine.insertRow(LeaveTB.TABLE_NAME, insertMap);
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean approve(LeaveTB leave){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(LeaveTB.APPFIRSTNAME_LEAVE, leave.getAppfirstname_leave());
		updateMap.put(LeaveTB.APPLASTNAME_LEAVE, leave.getApplastname_leave());
		updateMap.put(LeaveTB.APPNAME_LEAVE, leave.getAppname_leave());
		updateMap.put(LeaveTB.APPOPERATORID_LEAVE, leave.getAppoperatorid_leave());
		
		updateMap.put(LeaveTB.APPDATE_LEAVE, new StringBuffer("NOW()"));
		
		updateMap.put(LeaveTB.ISAPP_LEAVE, true);
		
		return sqlMachine.updateRow(LeaveTB.TABLE_NAME, updateMap, LeaveTB.ID_LEAVE, leave.getId_leave());
	
	}
	
	
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public boolean update(OperatorAttendanceTB operator){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(OperatorAttendanceTB.BASICPAY_OPERATOR, operator.getBasicpay_operator());
		
		updateMap.put(OperatorAttendanceTB.OPERATORID_OPERATOR, operator.getOperatorid_operator());
		updateMap.put(OperatorAttendanceTB.NAME_OPERATOR, operator.getName_operator());
		updateMap.put(OperatorAttendanceTB.LASTNAME_OPERATOR, operator.getLastname_operator());
		updateMap.put(OperatorAttendanceTB.FIRSTNAMENAME_OPERATOR, operator.getFirstname_operator());
		

		updateMap.put(OperatorAttendanceTB.MONDAY_OPERATOR, operator.isMonday_operator());
		updateMap.put(OperatorAttendanceTB.TUESDAY_OPERATOR, operator.isTuesday_operator());
		updateMap.put(OperatorAttendanceTB.WEDNESDAY_OPERATOR, operator.isWednesday_operator());
		updateMap.put(OperatorAttendanceTB.THURSDAY_OPERATOR, operator.isThursday_operator());
		updateMap.put(OperatorAttendanceTB.FRIDAY_OPERATOR, operator.isFriday_operator());
		updateMap.put(OperatorAttendanceTB.SATURDAY_OPERATOR, operator.isSaturday_operator());
		updateMap.put(OperatorAttendanceTB.SUNDAY_OPERATOR, operator.isSunday_operator());
	
		updateMap.put(OperatorAttendanceTB.WORKHOURS_OPERATOR, operator.getWorkhours_operator());
		updateMap.put(OperatorAttendanceTB.OFFHOURS_OPERATOR, operator.getOffhours_operator());
		
		return sqlMachine.updateRow(OperatorAttendanceTB.TABLE_NAME, updateMap, OperatorAttendanceTB.ID_OPERATOR, operator.getId_operator());
	}
	
	public boolean cancle(int id){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();

		updateMap.put(LeaveTB.ISCANCEL_LEAVE, true);

		return sqlMachine.updateRow(LeaveTB.TABLE_NAME, updateMap, LeaveTB.ID_LEAVE, id);
		
		
}
	
}
